﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Configuration;
using System.Data.Common;
using System.Data;

using CoyoEden.Models;

namespace CoyoEden.Providers
{
    public partial class DbPortalProvider
    {
        #region category
        /// <summary>
        /// Returns a category 
        /// </summary>
        /// <param name="id">Id of category to return</param>
        /// <returns></returns>
        public override Category SelectCategory(int id)
        {
            List<Category> categories = Category.Categories;

            Category category = new Category();

            foreach (Category cat in categories)
            {
                if (cat.Id == id)
                    category = cat;
            }
            category.MarkOld();
            return category;
        }

        /// <summary>
        /// Adds a new category to the database
        /// </summary>
        /// <param name="category">category to add</param>
        public override void InsertCategory(Category category)
        {
            List<Category> categories = Category.Categories;
            categories.Add(category);

            string connString = ConfigurationManager.ConnectionStrings[connStringName].ConnectionString;
            string providerName = ConfigurationManager.ConnectionStrings[connStringName].ProviderName;
            DbProviderFactory provider = DbProviderFactories.GetFactory(providerName);

            using (DbConnection conn = provider.CreateConnection())
            {
                conn.ConnectionString = connString;
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    string sqlQuery = "INSERT INTO " + tablePrefix + "Categories (CategoryName, description, slug,icon,ParentID,DisplayOrder) " +
                                                            "VALUES (@catname, @description,@slug,@icon, @parentid,@displayorder);";
                    sqlQuery += "SELECT CategoryID FROM " + tablePrefix + "Categories WHERE CategoryName=@catname;";
                    if (parmPrefix != "@")
                        sqlQuery = sqlQuery.Replace("@", parmPrefix);
                    cmd.CommandText = sqlQuery;
                    cmd.CommandType = CommandType.Text;

                    DbParameter dpTitle = provider.CreateParameter();
                    dpTitle.ParameterName = parmPrefix + "catname";
                    dpTitle.Value = category.Title;
                    cmd.Parameters.Add(dpTitle);

                    DbParameter dpDesc = provider.CreateParameter();
                    dpDesc.ParameterName = parmPrefix + "description";
                    dpDesc.Value = category.Description;
                    cmd.Parameters.Add(dpDesc);

                    DbParameter dpSlug = provider.CreateParameter();
                    dpSlug.ParameterName = parmPrefix + "slug";
                    dpSlug.Value = category.Slug;
                    cmd.Parameters.Add(dpSlug);

                    DbParameter dpIcon = provider.CreateParameter();
                    dpIcon.ParameterName = parmPrefix + "icon";
                    dpIcon.Value = category.Icon;
                    cmd.Parameters.Add(dpIcon);

                    DbParameter dpParent = provider.CreateParameter();
                    dpParent.ParameterName = parmPrefix + "parentid";
                    dpParent.Value = category.Parent;
                    cmd.Parameters.Add(dpParent);

                    DbParameter dpDisplayOrder = provider.CreateParameter();
                    dpDisplayOrder.ParameterName = parmPrefix + "displayorder";
                    dpDisplayOrder.Value = category.DisplayOrder;
                    cmd.Parameters.Add(dpDisplayOrder);
                    //cmd.ExecuteNonQuery();
                    var cid = cmd.ExecuteScalar();
                    category.Id = Convert.ToInt32(cid);
                }
            }
        }

        /// <summary>
        /// Saves an existing category to the database
        /// </summary>
        /// <param name="category">category to be saved</param>
        public override void UpdateCategory(Category category)
        {
            List<Category> categories = Category.Categories;
            categories.Remove(category);
            categories.Add(category);

            string connString = ConfigurationManager.ConnectionStrings[connStringName].ConnectionString;
            string providerName = ConfigurationManager.ConnectionStrings[connStringName].ProviderName;
            DbProviderFactory provider = DbProviderFactories.GetFactory(providerName);

            using (DbConnection conn = provider.CreateConnection())
            {
                conn.ConnectionString = connString;
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    string sqlQuery = "UPDATE " + tablePrefix + "Categories " +
                                                "SET CategoryName = @catname, " +
                                                "Description = @description,Slug=@slug,Icon=@icon, ParentID = @parentid, DisplayOrder=@displayorder " +
                                                "WHERE CategoryID = @catid";
                    if (parmPrefix != "@")
                        sqlQuery = sqlQuery.Replace("@", parmPrefix);
                    cmd.CommandText = sqlQuery;
                    cmd.CommandType = CommandType.Text;

                    DbParameter dpID = provider.CreateParameter();
                    dpID.ParameterName = parmPrefix + "catid";
                    dpID.Value = category.Id;
                    cmd.Parameters.Add(dpID);

                    DbParameter dpTitle = provider.CreateParameter();
                    dpTitle.ParameterName = parmPrefix + "catname";
                    dpTitle.Value = category.Title;
                    cmd.Parameters.Add(dpTitle);

                    DbParameter dpDesc = provider.CreateParameter();
                    dpDesc.ParameterName = parmPrefix + "description";
                    dpDesc.Value = category.Description;
                    cmd.Parameters.Add(dpDesc);

                    DbParameter dpSlug = provider.CreateParameter();
                    dpSlug.ParameterName = parmPrefix + "slug";
                    dpSlug.Value = category.Slug;
                    cmd.Parameters.Add(dpSlug);

                    DbParameter dpIcon = provider.CreateParameter();
                    dpIcon.ParameterName = parmPrefix + "icon";
                    dpIcon.Value = category.Icon;
                    cmd.Parameters.Add(dpIcon);

                    DbParameter dpParent = provider.CreateParameter();
                    dpParent.ParameterName = parmPrefix + "parentid";
                    dpParent.Value = category.Parent;
                    cmd.Parameters.Add(dpParent);

                    DbParameter dpDisplayOrder = provider.CreateParameter();
                    dpDisplayOrder.ParameterName = parmPrefix + "displayorder";
                    dpDisplayOrder.Value = category.DisplayOrder;
                    cmd.Parameters.Add(dpDisplayOrder);

                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Deletes a category from the database
        /// </summary>
        /// <param name="category">category to be removed</param>
        public override void DeleteCategory(Category category)
        {
            List<Category> categories = Category.Categories;
            categories.Remove(category);

            string connString = ConfigurationManager.ConnectionStrings[connStringName].ConnectionString;
            string providerName = ConfigurationManager.ConnectionStrings[connStringName].ProviderName;
            DbProviderFactory provider = DbProviderFactories.GetFactory(providerName);

            using (DbConnection conn = provider.CreateConnection())
            {
                conn.ConnectionString = connString;
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    string sqlQuery = "DELETE FROM " + tablePrefix + "PostCategory " +
                            "WHERE CategoryID = " + parmPrefix + "catid;" +
                            "DELETE FROM " + tablePrefix + "Categories " +
                            "WHERE CategoryID = " + parmPrefix + "catid";
                    cmd.CommandText = sqlQuery;
                    cmd.CommandType = CommandType.Text;

                    DbParameter dpID = provider.CreateParameter();
                    dpID.ParameterName = parmPrefix + "catid";
                    dpID.Value = category.Id;
                    cmd.Parameters.Add(dpID);

                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Gets all categories in database
        /// </summary>
        /// <returns>List of categories</returns>
        public override List<Category> FillCategories()
        {
            List<Category> categories = new List<Category>();
            string connString = ConfigurationManager.ConnectionStrings[connStringName].ConnectionString;
            string providerName = ConfigurationManager.ConnectionStrings[connStringName].ProviderName;
            DbProviderFactory provider = DbProviderFactories.GetFactory(providerName);

            using (DbConnection conn = provider.CreateConnection())
            {
                conn.ConnectionString = connString;

                using (DbCommand cmd = conn.CreateCommand())
                {
                    string sqlQuery = "SELECT CategoryID, CategoryName, description,Slug,Icon,ParentID,DisplayOrder " +
                            "FROM " + tablePrefix + "Categories ";
                    cmd.CommandText = sqlQuery;
                    cmd.CommandType = CommandType.Text;
                    conn.Open();

                    using (DbDataReader rdr = cmd.ExecuteReader())
                    {
                        if (rdr.HasRows)
                        {
                            while (rdr.Read())
                            {
                                Category cat = new Category();
                                cat.Title = rdr.GetString(1);
                                cat.Description = (rdr.IsDBNull(2)) ? "" : rdr.GetString(2);
                                cat.Slug = (rdr.IsDBNull(3)) ? "" : rdr.GetString(3);
                                cat.Icon = (rdr.IsDBNull(4)) ? "" : rdr.GetString(4);
                                cat.Parent = (rdr.IsDBNull(5)) ? -1 : rdr.GetInt32(5);
                                cat.Id = rdr.GetInt32(0);
                                cat.DisplayOrder = (rdr.IsDBNull(6)) ? 0 : rdr.GetInt32(6);
                                categories.Add(cat);
                                cat.MarkOld();
                            }
                        }
                    }
                }
            }

            return categories;
        }
        #endregion
    }
}
